"""
Case Type   : 系统视图PG_PREPARED_STATEMENT
Case Name   : 审计管理员查询函数，合理报错
Create At   : 2023/12/7
Owner       : ningyali
Description :
    1.建表
    2.创建审计管理员
    3.使用审计管理员连接数据库，查询视图
    4.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.连接成功，查询失败，提示不允许调用
    4.执行成功
History     :
"""

import os
import unittest

from yat.test import macro

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class PgPreparedStatement24(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_prepare_24'
        self.u_name = 'u_prepare_24'
        self.query_sql = 'select * from pg_prepared_statement(0) ' \
                         'order by 1, 2;'

    def test_1(self):
        text = 'step1:建表; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};' \
            f'create table {self.t_name}(c1 int, c2 text);'
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, f'执行失败：{text}')

        text = 'step2:创建审计管理员; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop user if exists {self.u_name};' \
            f'create user {self.u_name} with auditadmin ' \
            f'password \\"{macro.COMMON_PASSWD}\\";'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, res,
                      f'执行失败：{text}')

        text = 'step3:使用审计管理员连接数据库，查询视图; ' \
               'expect:连接成功，查询失败，提示不允许调用'
        self.log.info(f'-----{text}-----')
        cmd = f'source {macro.DB_ENV_PATH};' \
            f'gsql -d {self.pri_sh.node.db_name} -U {self.u_name} ' \
            f'-W "{macro.COMMON_PASSWD}" -c "{self.query_sql}"'
        self.log.info(cmd)
        res = self.pri_sh.node.sh(cmd).result()
        self.log.info(res)
        expect = "ERROR:  permission denied for function " \
                 "pg_prepared_statements"
        self.assertIn(expect, res, f'执行失败：{text}')

    def tearDown(self):
        self.log.info('----run teardown----')
        text1 = 'step4:清理环境; expect:执行成功'
        self.log.info(f'-----{text1}-----')
        sql = f'drop table if exists {self.t_name} cascade;' \
            f'drop user if exists {self.u_name};'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text1}')
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, res,
                      f'执行失败：{text1}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
